db.users.insertOne({name: "Rajkumar",address: "16 st james garden",phone_number: "7881143522",email: "rajkumar1@gmail.com",date_joined: new Date("2023-11-10")});
db.courses.insertOne({subject: "Math 101",description: "Introduction to Mathematics",start_date: new Date("2024-01-10"),end_date: new Date("2024-05-20"),instructor: "Dr. John Smith"});
db.transactions.insertOne({transaction_id: "T001",user_id: 'U1',course_id: 'C01',transaction_date: new Date("2024-01-15"),amount: 1000,status: "completed"});
db.users.insertOne({name: "Rajashekar",address: "19 kings road",phone_number: "7881142511",email: "rajashekar2@gmail.com",date_joined: new Date("2023-06-09")});
db.courses.insertOne({name: "Physics 101",description: "Basic Physics Concepts",start_date: new Date("2024-02-01"),end_date: new Date("2024-06-30"),instructor: "Dr. Jane Doe"});
db.transactions.insertOne({transaction_id: "T002",user_id: 'U2', course_id: 'C02',transaction_date: new Date("2024-02-10"),amount: 1200,status: "completed"});
db.users.insertOne({name: "Rajesh",address: "23 st helens",phone_number: "7955436879",email: "rajesh3@gmail.com",date_joined: new Date("2024-09-20")});
db.courses.insertOne({name: "Chemistry 101",description: "Fundamentals of Chemistry",start_date: new Date("2024-03-01"),end_date: new Date("2024-07-15"),instructor: "Dr. Robert Brown"});
db.transactions.insertOne({transaction_id: "T003",user_id: 'U3',course_id: 'C03',transaction_date: new Date("2024-03-05"),amount: 1100,status: "pending"});
db.users.insertOne({name: "Raghupathi",address: "32 henreitt Street",phone_number: "7945855432",email: "raghupathi4@gmail.com",date_joined: new Date("2024-01-22")});
db.courses.insertOne({name: "Biology 101",description: "Introduction to Biology",start_date: new Date("2024-04-01"),end_date: new Date("2024-08-15"),instructor: "Dr. Alice Green"});
db.transactions.insertOne({transaction_id: "T004",user_id: 'U4',course_id: 'C04',transaction_date: new Date("2024-04-01"),amount: 1300,status: "completed"});
db.users.insertOne({name: "Nikhilvarma",address: "58 catherine St",phone_number: "7785542210",email: "nikhilvarma5@gmail.com",date_joined: new Date("2024-05-19")});
db.courses.insertOne({name: "Computer Science 101",description: "Introduction to Computer Science",start_date: new Date("2024-05-01"),end_date: new Date("2024-09-10"),instructor: "Dr. Samuel White"});
db.transactions.insertOne({transaction_id: "T005",user_id: 'U5',course_id: 'C05',transaction_date: new Date("2024-05-10"),amount: 1500,status: "failed"});
db.users.insertOne({name: "Kamalakar",address: "18 edwards road",phone_number: "7668943321",email: "kamalakar6@email.com",date_joined: new Date("2023-07-13")});
db.courses.insertOne({name: "Economics 101",description: "Introduction to Economics",start_date: new Date("2024-06-01"),end_date: new Date("2024-10-30"),instructor: "Dr. Linda Harris"});
db.transactions.insertOne({transaction_id: "T006",user_id: 'U6',course_id: 'C06',transaction_date: new Date("2024-06-05"),amount: 800,status: "completed"});
db.users.insertOne({name: "Vishnu",address: "45 kings way",phone_number: "7605311832",email: "vishnu7@gmail.com",date_joined: new Date("2024-10-15")});
db.courses.insertOne({name: "History 101",description: "World History",start_date: new Date("2024-07-01"),end_date: new Date("2024-11-15"),instructor: "Dr. Mark Taylor"});
db.transactions.insertOne({transaction_id: "T007",user_id: 'U7',course_id: 'C07',transaction_date: new Date("2024-07-05"),amount: 950,status: "completed"});
db.users.insertOne({name: "Varma",address: "49 mumbles",phone_number: "7552110943",email: "varma8@gmail.com",date_joined: new Date("2024-02-26")});
db.courses.insertOne({name: "English 101",description: "English Literature",start_date: new Date("2024-08-15"),end_date: new Date("2024-12-25"),instructor: "Dr. Emily Clark"});
db.transactions.insertOne({transaction_id: "T008",user_id: 'U8',course_id: 'C08',transaction_date: new Date("2024-08-20"),amount: 1100,status: "pending"});
db.users.insertOne({name: "Prathamesh",address: "65 james street",phone_number: "7552199087",email: "prathaesh9@gmail.com",date_joined: new Date("2023-04-20")});
db.courses.insertOne({name: "Political Science 101",description: "Introduction to Political Science",start_date: new Date("2024-05-10"),end_date: new Date("2024-09-15"),instructor: "Dr. Linda Martin"});
db.transactions.insertOne({transaction_id: "T009",user_id: 'U9',course_id: 'C09',transaction_date: new Date("2024-05-12"),amount: 1050,status: "completed"});
db.users.insertOne({name: "Ajay",address: "73 morriston",phone_number: "7092884315",email: "ajay10@gmail.com",date_joined: new Date("2024-03-25")});
db.courses.insertOne({name: "Sociology 101",description: "Introduction to Sociology",start_date: new Date("2024-06-15"),end_date: new Date("2024-11-05"),instructor: "Dr. Andrew Lee"});
db.transactions.insertOne({transaction_id: "T010",user_id: 'U10',course_id: 'C10',transaction_date: new Date("2024-06-20"),amount: 1150,status: "completed"});


// MongoDB find() - Retrieve all users
db.users.find();

// MongoDB find() with filter - Retrieve specific users who joined after 2024-01-01
db.users.find({ date_joined: { $gte: new Date("2024-01-01") } });

// MongoDB equivalent of a SQL JOIN (using aggregation pipeline)
db.transactions.aggregate([
  {
    $lookup: {
      from: "users",  // The collection to join with
      localField: "user_id",  // Field from the current collection
      foreignField: "user_id",  // Field from the 'users' collection
      as: "user_info"  // Result will be an array with user data
    }
  }
]);

// MongoDB equivalent of a SQL JOIN (using aggregation pipeline)
db.transactions.aggregate([
  {
    $lookup: {
      from: "users",  // The collection to join with
      localField: "user_id",  // Field from the current collection
      foreignField: "user_id",  // Field from the 'users' collection
      as: "user_info"  // Result will be an array with user data
    }
  }
]);

-- SQL SELECT statement - Retrieve all users
SELECT * FROM users;

-- SQL SELECT with a condition - Retrieve users who joined after 2024-01-01
SELECT * FROM users WHERE date_joined >= '2024-01-01';

-- SQL INNER JOIN - Join 'users' and 'transactions' tables based on user_id
SELECT users.name, transactions.amount
FROM users
INNER JOIN transactions ON users.user_id = transactions.user_id;

// MongoDB aggregation with grouping and summing
db.transactions.aggregate([
  {
    $group: {
      _id: "$user_id",  // Group by user_id
      totalAmount: { $sum: "$amount" }  // Sum the 'amount' field
    }
  }
]);

-- SQL Aggregate Query with GROUP BY and SUM
SELECT user_id, SUM(amount) AS totalAmount
FROM transactions
GROUP BY user_id;

db.users.insertOne({
  name: "Rajkumar",
  address: "16 st james garden",
  phone_number: "7881143522",
  email: "rajkumar1@gmail.com",
  date_joined: new Date("2023-11-10"),
  transactions: [
    { course_id: "C01", amount: 1000, status: "completed" },
    { course_id: "C02", amount: 1200, status: "completed" }
  ]
});


